﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;
using DingY.DB;

namespace DotNet.WinUI
{
    public class BaseForm : Form
    {
        public BaseForm()
        {

        }

        #region 数据处理

        /// <summary>
        /// 执行增、删、改的数据操作
        /// </summary>
        /// <param name="Sql">T-SQL语句</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string Sql)
        {
            if (Sql.Length == 0)
            {
                return -1;
            }

            try
            {
                SqlDB objSqlDB = new SqlDB();
                return objSqlDB.ExecuteSql(Sql);
            }
            catch (Exception ex)
            {
                ShowMsg(ex.Message);
                return -1;
            }
        }

        /// <summary>
        /// 获取数据库的唯一值
        /// </summary>
        /// <param name="Sql">T-SQL语句</param>
        /// <returns>唯一值对象</returns>
        public object ExecuteScalar(string Sql)
        {
            if (Sql.Length == 0)
            {
                return -1;
            }

            try
            {
                SqlDB objSqlDB = new SqlDB();
                return objSqlDB.ExecuteScalar(Sql);
            }
            catch (Exception ex)
            {
                ShowMsg(ex.Message);
                return -1;
            }
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="Sql">T-SQL语句</param>
        /// <returns>数据集</returns>
        public DataSet ExecuteDataSet(string Sql)
        {
            if (Sql.Length == 0)
            {
                return null;
            }

            try
            {
                SqlDB objSqlDB = new SqlDB();
                return objSqlDB.ExecuteDataSet(Sql);
            }
            catch (Exception ex)
            {
                ShowMsg(ex.Message);
                return null;
            }
        }

        #endregion

        #region 导出Excel

        public void ExportToExcel(DataSet dataSet, string SheetName)
        {
            try
            {
                string _OuputPath = "";
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "Execl工作簿(*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                saveFileDialog.FileName = SheetName;
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    _OuputPath = saveFileDialog.FileName;
                }
                if (string.IsNullOrEmpty(_OuputPath))
                {
                    return;
                }

                //创建Excel实例
                //ApplicationClass excelApp = new ApplicationClass();  
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                //保存时不提示 
                excelApp.DisplayAlerts = false;
                //System.Diagnostics.Process xlPrc;  
                System.Diagnostics.Process[] xlPrcArray;
                xlPrcArray = System.Diagnostics.Process.GetProcessesByName("EXCEL");

                //确定新建Excel文件的进程
                System.Diagnostics.Process newExcelPrc = xlPrcArray[0];
                for (int i = 0; i <= xlPrcArray.Length - 1; i++)
                {
                    if (xlPrcArray[i].TotalProcessorTime.TotalSeconds < newExcelPrc.TotalProcessorTime.TotalSeconds)
                    {
                        newExcelPrc = xlPrcArray[i];
                    }
                }

                // Create a new Excel Workbook 
                Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
                int sheetIndex = 0;

                // Copy each DataTable 
                foreach (System.Data.DataTable dt in dataSet.Tables)
                {
                    // Copy the DataTable to an object array  
                    object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

                    // Copy the column names to the first row of the object array  
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        rawData[0, col] = dt.Columns[col].ColumnName;
                    }

                    // Copy the values to the object array  
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        //字符型前面要加上：'  
                        if (dt.Columns[col].DataType == System.Type.GetType("System.String"))
                        {
                            for (int row = 0; row < dt.Rows.Count; row++)
                            {
                                rawData[row + 1, col] = "'" + dt.Rows[row].ItemArray[col].ToString();
                            }
                        }
                        else
                        {
                            for (int row = 0; row < dt.Rows.Count; row++)
                            {
                                rawData[row + 1, col] = dt.Rows[row].ItemArray[col].ToString();
                            }
                        }
                    }

                    // Calculate the final column letter 
                    string finalColLetter = string.Empty;
                    string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                    int colCharsetLen = colCharset.Length;
                    if (dt.Columns.Count > colCharsetLen)
                    {
                        finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1);
                    }
                    finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);

                    // Create a new Sheet  
                    Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex), Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
                    excelSheet.Name = SheetName;// dt.TableName;

                    // Fast data export to Excel
                    string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);

                    excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

                    // Mark the first row as BOLD
                    ((Microsoft.Office.Interop.Excel.Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
                }

                // Save and Close the Workboo
                excelWorkbook.SaveAs(_OuputPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                excelWorkbook.Close(true, Type.Missing, Type.Missing);
                excelWorkbook = null;

                //// Release the Application object 
                excelApp.Quit();
                excelApp = null;
                // Collect the unreferenced objects
                GC.Collect();
                GC.WaitForPendingFinalizers();
                newExcelPrc.Kill();
                //打开刚才导出的文件  
                System.Diagnostics.Process.Start(_OuputPath);
            }
            catch (Exception ex)
            {
                ShowMsg(ex.Message);
            }
        }

        #endregion

        #region 消息提示

        public void ShowMsg(string Msg)
        {
            MessageBox.Show(Msg, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        public DialogResult ShowQuestionMsg(string Msg)
        {
            return MessageBox.Show(Msg, "确认信息", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
        }

        #endregion

        public void FillDataToGridView(DataGridView dg, string sql)
        {
            SqlDB objSqlDB = new SqlDB();
            dg.DataSource = objSqlDB.ExecuteDataSet(sql).Tables[0].DefaultView;
            //设置控件属性
            dg.ReadOnly = true;
            dg.AllowUserToAddRows = false;
            dg.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            dg.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
            dg.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        }

        public string GetUsersName(string LoginID)
        {
            try
            {
                string _Sql = @"SELECT Name 
                    FROM Users 
                    WHERE LoginID = '{0}' ";
                _Sql = string.Format(_Sql, LoginID);

                SqlDB objSqlDB = new SqlDB();
                return objSqlDB.ExecuteScalar(_Sql).ToString();
            }
            catch
            {
                return "非法用户";
            }
        }

        public void FillIndustry(System.Windows.Forms.ComboBox cmb, string DefaultText)
        {
            string _Sql = @"SELECT IndustryID, Name
                FROM Industry ";
            SqlDB objSqlDB = new SqlDB();

            DataTable table = objSqlDB.ExecuteDataTable(_Sql);
            DataRow dr = table.NewRow();
            dr["IndustryID"] = "0";
            dr["Name"] = DefaultText;
            table.Rows.InsertAt(dr, 0);
            cmb.DataSource = table;
            cmb.ValueMember = "IndustryID";
            cmb.DisplayMember = "Name";
            cmb.SelectedIndex = 0;
        }

        public bool ValidatePhone(string Input)
        {
            string regex = @"^(13|14|15|18)\d{9}$";
            return System.Text.RegularExpressions.Regex.IsMatch(Input, regex);
        }
    }
}